import xlwt
import xlrd
from xlwt.Workbook import Workbook
from db import db1


def excel(sql,lst,filename):
    data=db1.find(sql)
    # 创建工作簿
    f=xlwt.Workbook()
    # 添加sheet
    worksheet=f.add_sheet('sheet1',cell_overwrite_ok=True)

    i=0
    while i < len(data[0]):
        # 参数行开始，行结束，列开始，列结束，列表标题
        worksheet.write_merge(0,0,i,i,lst[i])
        i+=1

    # 内容写入
    j=0
    while j<len(data[0]):
        k=0
        while k<len(list(data[j].values())):
            worksheet.write_merge(j+1,j+1,k,k,list(data[j].values())[k])
            k+=1
        j+=1
    f.save('D:\p9\pro\workflow\static\download/'+filename)


# sql="select name,bianm,paix,content from gangwgl"
# lst=['岗位名称','岗位编码','岗位排序','岗位描述']
# filename='filess.xls'
# excel(sql,lst,filename)


def excelr(sql,filename,num1):
    # 打开excel文件
    Workbook=xlrd.open_workbook(filename)
    # 获取sheel1的名称
    sheet1_name=Workbook.sheet_names()[0]
    # 根据名称获取sheet内容
    sheet1=Workbook.sheet_by_name(sheet1_name)
    # 获取行数
    rows_num=sheet1.nrows

    list1=[]
    for i in range(1,rows_num):
        list1.append(tuple(sheet1.row_values(i)[:num1]))
    values=f"{list1}"[1:-1]
    sql1=sql+values
    db1.insert(sql1)




